package com.example.demo.dao.db1;

import com.example.demo.entity.ErpUser;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author ggkt
 * @date 2020/12/31 14:53
 */
public interface UserDao {
    /**
    一天内入职用户；
     */
    @Select("select a.accountId,a.accountNo,u.name,u.email,u.createTime,u.updateTime,u.userid,\n" +
            "CASE \n" +
            "      WHEN LOCATE(\"111437050\",d.parentIds)>0 THEN\n" +
            "          \"111437050\"\n" +
            "      WHEN LOCATE(\"111437049\",d.parentIds)>0 THEN\n" +
            "            '111437049'\n" +
            "      WHEN LOCATE(\"140668118\",d.parentIds)>0 THEN\n" +
            "            '140668118'\n" +
            "      WHEN LOCATE(\"140190568\",d.parentIds)>0 THEN\n" +
            "            '140190568'\n" +
            "      WHEN LOCATE(\"140690142\",d.parentIds)>0 THEN\n" +
            "            '140690142'\n" +
            "      WHEN LOCATE(\"140664090\",d.parentIds)>0 THEN\n" +
            "            '140664090'\n" +
            "      WHEN LOCATE(\"140665149\",d.parentIds)>0 THEN\n" +
            "            '140665149'\n" +
            "  END as deptId\n" +
            " from dingtalk_users  u \n" +
            "LEFT JOIN erp_account a  on u.accountId = a.accountid and u.companyId = a.companyId\n" +
            "LEFT JOIN dingtalk_dept d on u.mainDeptId = d.orgId\n" +
            "where (d.parentIds LIKE '%111437050%' or  d.parentIds LIKE '%111437049%' or d.parentIds LIKE '%140668118%' or\n" +
            " d.parentIds LIKE '%140190568%' or  d.parentIds LIKE '%140690142%' or  d.parentIds LIKE '%140664090%' or \n" +
            "d.parentIds LIKE '%140665149%') and a.accountId is not null " +
            "and datediff(u.createTime,now())=-1\n" +
            "order by createTime asc ")
    List<ErpUser> selectUsers() throws Exception;

    /**
     * 一天内离职用户
     * @param time
     * @return
     * @throws Exception
     */
    @Select("select a.accountId,a.accountNo,u.name,u.email,\n" +
            "CASE \n" +
            "      WHEN LOCATE(\"111437050\",d.parentIds)>0 THEN\n" +
            "          \"111437050\"\n" +
            "      WHEN LOCATE(\"111437049\",d.parentIds)>0 THEN\n" +
            "            '111437049'\n" +
            "      WHEN LOCATE(\"140668118\",d.parentIds)>0 THEN\n" +
            "            '140668118'\n" +
            "      WHEN LOCATE(\"140190568\",d.parentIds)>0 THEN\n" +
            "            '140190568'\n" +
            "      WHEN LOCATE(\"140690142\",d.parentIds)>0 THEN\n" +
            "            '140690142'\n" +
            "      WHEN LOCATE(\"140664090\",d.parentIds)>0 THEN\n" +
            "            '140664090'\n" +
            "      WHEN LOCATE(\"140665149\",d.parentIds)>0 THEN\n" +
            "            '140665149'\n" +
            "\t  WHEN LOCATE(\"276126018\",d.parentIds)>0 THEN\n" +
            "            '276126018' \n" +
            "  END as deptId,u.leaveDate\n" +
            " from dingtalk_users  u \n" +
            "LEFT JOIN erp_account a  on u.accountId = a.accountid and u.companyId = a.companyId\n" +
            "LEFT JOIN dingtalk_dept d on u.mainDeptId = d.orgId\n" +
            "where (d.parentIds LIKE '%111437050%' or  d.parentIds LIKE '%111437049%' \n" +
            "or d.parentIds LIKE '%140668118%' or\n" +
            " d.parentIds LIKE '%140190568%' or  d.parentIds LIKE '%140690142%' \n" +
            " or  d.parentIds LIKE '%140664090%' or \n" +
            "d.parentIds LIKE '%140665149%' ) and  a.accountId is not null and u.status=1\n" +
            "and leaveDate>#{time}")
    List<ErpUser> selectLeaveUsersByTime(long time) throws Exception;


    /**
    查询所有用户；
     */
    @Select("select u.userid,a.accountId,a.accountNo,u.name,u.email,u.createTime,u.updateTime,\n" +
            "CASE \n" +
            "      WHEN LOCATE(\"111437050\",d.parentIds)>0 THEN\n" +
            "          \"111437050\"\n" +
            "      WHEN LOCATE(\"111437049\",d.parentIds)>0 THEN\n" +
            "            '111437049'\n" +
            "      WHEN LOCATE(\"140668118\",d.parentIds)>0 THEN\n" +
            "            '140668118'\n" +
            "      WHEN LOCATE(\"140190568\",d.parentIds)>0 THEN\n" +
            "            '140190568'\n" +
            "      WHEN LOCATE(\"140690142\",d.parentIds)>0 THEN\n" +
            "            '140690142'\n" +
            "      WHEN LOCATE(\"140664090\",d.parentIds)>0 THEN\n" +
            "            '140664090'\n" +
            "      WHEN LOCATE(\"140665149\",d.parentIds)>0 THEN\n" +
            "            '140665149'\n" +
            "  END as deptId\n" +
            " from dingtalk_users  u \n" +
            "LEFT JOIN erp_account a  on u.accountId = a.accountid and u.companyId = a.companyId\n" +
            "LEFT JOIN dingtalk_dept d on u.mainDeptId = d.orgId\n" +
            "where (d.parentIds LIKE '%111437050%' or  d.parentIds LIKE '%111437049%' or d.parentIds LIKE '%140668118%' or\n" +
            " d.parentIds LIKE '%140190568%' or  d.parentIds LIKE '%140690142%' or  d.parentIds LIKE '%140664090%' or \n" +
            "d.parentIds LIKE '%140665149%') and a.accountId is not null " +
            "order by accountId asc ")
    List<ErpUser> selectAllUsers() throws Exception;

}
